package com.firmboy.mysql;

import com.firmboy.mysql.db.DBHelper;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * mysql的读已提交和可重复读之间的区别，可重复读，再一个事务中
 *
 * @author firmboy
 * @create 2018-05-05 下午12:28
 **/
public class MysqlGapLock {


    @Test
    public void ATest() throws SQLException {

        Connection conn = DBHelper.getConn();

        //设置可重复读
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

        //设置读已提交
        //conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        conn.setAutoCommit(false);

        PreparedStatement ps = conn.prepareStatement("SELECT count(*) from test_lock where id >=3 and id<=7");

        while (true){
            ResultSet resultSet = ps.executeQuery();
            resultSet.next();
            int anInt = resultSet.getInt(1);

            System.out.println("count:"+anInt);
        }


    }

    @Test
    public void BTest() throws SQLException {
        Connection conn = DBHelper.getConn();

        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

        conn.setAutoCommit(false);

        PreparedStatement ps = conn.prepareStatement("insert into test_lock VALUES (4,2,3)");
        ps.execute();
        conn.commit();
    }



}
